******************************************
// The do-file prepares the ADA score data to match with election data by:name surname state and election year
******************************************

// Import data downloaded from: https://dataverse.harvard.edu/dataset.xhtml?persistentId=hdl:1902.1/12339


import excel ".../Anderson Habel Adjusted ADA 1947-2007_1.xls", sheet("Scores") firstrow clear case(lower)


recode chamber (2=0) , gen(house)
label variable house "House 1 Senate 0"

rename nominalada ada
rename adjustedada real_ada

foreach n in 1 2 {
g ada`n'=ada
g real_ada`n'=real_ada
}

keep if house==1

replace statenam=trim(statenam)
replace namel=trim(namel)

g congress=0
replace congress=114 if year==2015 | year==2016
replace congress=113 if year==2013 | year==2014
replace congress=112 if year==2011 | year==2012
replace congress=111 if year==2009 | year==2010
replace congress=110 if year==2007 | year==2008
replace congress=109 if year==2005 | year==2006
replace congress=108 if year==2003 | year==2004
replace congress=107 if year==2001 | year==2002
replace congress=106 if year==1999 | year==2000
replace congress=105 if year==1997 | year==1998
replace congress=104 if year==1995 | year==1996
replace congress=103 if year==1993 | year==1994
replace congress=102 if year==1991 | year==1992
replace congress=101 if year==1989 | year==1990
replace congress=100 if year==1987 | year==1988
replace congress=99 if year==1985 | year==1986
replace congress=98 if year==1983 | year==1984
replace congress=97 if year==1981 | year==1982
replace congress=96 if year==1979 | year==1980
replace congress=95 if year==1977 | year==1978
replace congress=94 if year==1975 | year==1976
replace congress=93 if year==1973 | year==1974
replace congress=92 if year==1971 | year==1972
replace congress=91 if year==1969 | year==1970
replace congress=90 if year==1967 | year==1968
replace congress=89 if year==1965 | year==1966
replace congress=88 if year==1963 | year==1964
replace congress=87 if year==1961 | year==1962
replace congress=86 if year==1959 | year==1960
replace congress=85 if year==1957 | year==1958
replace congress=84 if year==1955 | year==1956
replace congress=83 if year==1953 | year==1954
replace congress=82 if year==1951 | year==1952
replace congress=81 if year==1949 | year==1950
replace congress=80 if year==1947 | year==1948

tab year if congress==0


sort congress year 


replace namel="ISTOOK, ERNEST JIM" if namel=="ISTOOK, ERNEST JIM, JR." & congress==104 

// collapse
collapse (mean) real_ada ada (firstnm) statenum statenam party real_ada1 ada1 names (lastnm) real_ada2 ada2 ///
, by (congress namel)


label variable real_ada "Real ADA congress avg"
label variable real_ada1 "Real ADA 1st year"
label variable real_ada2 "Real ADA 2nd year"
label variable ada "ADA congress avg"
label variable ada1 "ADA 1st year"
label variable ada2 "ADA 2nd year"

foreach var in namel names statenam {
replace `var'=trim(`var')
}

split namel, p(",") limit(2)

//surname
g surname = namel1 
replace surname=lower(surname)

label variable  surname "Surname"
label variable  namel "Name long"
label variable  names "Name short"
 
 //state names spelled wrong
g state=lower(statenam)
replace state="arkansas" if state=="arkansa"
replace state="california" if state=="califor"
replace state="california" if state=="calif"
replace state="colorado" if state=="colorad"
replace state="connecticut" if state=="connect"
replace state="delaware" if state=="delawar"
replace state="illinois" if state=="illinoi"
replace state="kentucky" if state=="kentuck"
replace state="louisiana" if state=="louisia"
replace state="maryland" if state=="marylan"
replace state="massachusetts" if state=="massach"
replace state="michigan" if state=="michiga"
replace state="minnesota" if state=="minneso"
replace state="mississippi" if state=="mississ"
replace state="missouri" if state=="missour"
replace state="nebraska" if state=="nebrask"
replace state="new hampshire" if state=="new ham"
replace state="new mexico" if state=="new mex"
replace state="new jersey" if state=="new jer"
replace state="new york" if state=="new yor"
replace state="north carolina" if state=="north c"
replace state="north dakota" if state=="north d"
replace state="oklahoma" if state=="oklahom"
replace state="pennsylvania" if state=="pennsyl"
replace state="rhode island" if state=="rhode i"
replace state="south carolina" if state=="south c"
replace state="south dakota" if state=="south d"
replace state="tennessee" if state=="tenness"
replace state="virginia" if state=="virgini"
replace state="washington" if state=="washing"
replace state="west virginia" if state=="west vi"
replace state="wisconsin" if state=="wiscons"

label variable state "State Name"
drop statenum statenam

//change surnames manually
replace surname="waggonner" if surname=="waggoner" & state=="louisiana"
replace surname="visclosky" if surname=="viscloskey" & state=="indiana"
replace surname="vander jagt" if surname=="van der jagt" & state=="michigan"
replace surname="vanpelt" if surname=="van pelt" & state=="wisconsin"
replace surname="vanzandt" if surname=="van zandt" & state=="pennsylvania"
replace surname="vandeerlin" if surname=="van deerlin" & state=="california"
replace surname="melancon" if surname=="melanchon" & state=="louisiana" & congress==110
replace surname="mcmorris" if surname=="mcmorris-rogers" & state=="washington" & congress==110
replace surname="inglis" if surname=="ingiss" & state=="south carolina" & congress==110
replace surname="frelinghuysen" if surname=="freylinghuysen" & state=="new jersey" & congress==110
replace surname="cuellar" if surname=="cueller" & state=="texas" & congress==110
replace surname="crowley" if surname=="crowlye" & state=="new york" & congress==110
replace surname="schwarz" if surname=="scwarz" & state=="michigan" & congress==109
replace surname="melancon" if surname=="melachon" & state=="louisiana" & congress==109
replace surname="inglis" if surname=="ingiss" & state=="south carolina" & congress==109
replace surname="frelinghuysen" if surname=="freylinghuysen" & state=="new jersey" & congress==109
replace surname="cuellar" if surname=="cueller" & state=="texas" & congress==109
replace surname="crowley" if surname=="crowlye" & state=="new york" & congress==109

//party
recode party (2=0) (3=.), g(dem_anderson)
drop party
label variable dem_and "Dem 1 Rep 0"

// cases with same name
g surname_state_congress = surname+"_"+state+"_"+string(congress)
sort surname_state_congress
g omonimo=1 if surname_state_congress==surname_state_congress[_n-1] | surname_state_congress==surname_state_congress[_n+1]
g firstname = lower(namel2) if omonimo==1
replace firstname=trim(firstname)


// change some firstnames manually
replace first="bob" if first=="robert carleton" & surname=="wilson"
replace first="charles h" if first=="charles h." & surname=="wilson"


g surname_state_congress_first = surname_state_congress+"_"+firstname

drop namel1 namel2

*save for further use
save "", replace




